Re: [SQL] Primary keys: can they be alphanumerical?

At 16:25 +0300 on 6/7/98, Brett W. McCoy wrote:


> On Mon, 6 Jul 1998, Stuart Rison wrote:
>
> > I would like to know what is the general concensus on having alphanumerical
> > primary keys.
>
> I use alphanumeric primary keys all the time.  I keep large databases
> with FDA regulatory information in them, and the FDA assigned approval
> number, usually a letter, followed by two digits indicating the year of
> approval or the type of approval (like for a generic drug), followed by four
> digits indicating the sequence of apporval for that year.  I use this
> same key as a a foreign key for, say, patent records, which is usuallu a
> one-to-many join.

To add: if the data in the primary key is already in other fields (that is,
if you have the referring hospital field, the patient number, etc. In other
fields - you can create a multi-field key. At least from the point of view
of classical RDBMS theory, it's perfectly acceptable. It saves redundancy.

However, if your intention is to use it in a primary-foreign connection -
that is, if you want to use this primary key as a foreign key in another
table, you can now see for yourself that it would be redundant. That is, if
your key is made of three fields, and it's actually repeated in every row
that relies on this key, you have a lot of redundancy. The redundancy
doesn't get smaller just because you change three fields into one
concatenated one...

In that case, I think the ideal thing to do is to create a numerical
primary key, used merely for relational purposes. You can still create an
index on the combination of fields that interests your user, or several
indices if they want to query by some, not all, of the fields you
mentioned. They need never refer to the actual primary key field. Your job
is to make them query by one index, and get the relations by another.

Of course, then, when they want to query on a related table, not on the
main table, and they want to use the referral information, you have to do a
join rather than a direct query. In that case, you're to decide: is the
redundancy problem serious enough to sacrifice speed? Will you need the
join anyway, because that's how the queries happen to go (always referring
to the father table)?

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma